use master
go

create database Student
on
(
   name='Student',
   filename='E:\Student.mdf',
   size=5MB,
   maxsize=20MB,
   filegrowth=10%
)
log on 
(
   name='Student_log',
   filename='E:\Student_log.ldf',
   size=5MB,
   maxsize=20MB,
   filegrowth=10%
)
go

use Student
go

create table Stuinfo
(
   StuID int identity(1,1),
   StuNO varchar(10) primary key not null,
   StuName nvarchar(10) not null,
   StuAge int,
   StuAddress nvarchar(20) not null,
   StuSeat int,
   StuSex nchar(1) check(StuSex='男' or StuSex='女')
)

create table StuExam
(
   ExamNo int primary key identity(1,1),
   StuNo varchar(10) references Stuinfo(StuNo),
   WrittenExam int,
   LabExam int
)
go

insert into Stuinfo values ('s2501','张狄利',20,'美国硅谷',1,'女'),('s2502','李斯文',18,'湖北武汉',2,'男'),('s2503','马文才',22,'湖南长沙',3,'女'),('s2504','欧阳俊雄',21,'湖北武汉',4,'男'),('s2505','梅超风',20,'湖北武汉',5,'女'),('s2506','陈旋风',19,'美国硅谷',6,'女'),('s2507','陈风',20,'美国硅谷',7,'男')

insert into StuExam values ('s2501',50,70),('s2502',60,65),('s2503',86,85),('s2504',40,80),('s2505',70,90),('s2506',85,90)
go

--查询每个地方的学生的平均年龄
select AVG(StuAge) 平均年龄, StuAddress from Stuinfo group by StuAddress
--查询男女生的分别的年龄总和
select '男生总数',SUM(StuAge)年龄总和 from Stuinfo where StuSex='男'
UNION
select '女生总数',SUM(StuAge)年龄总和 from Stuinfo where StuSex='女'
--查询每个地方的男女生的平均年龄和年龄的总和
select '男生总数', AVG(StuAge) 平均年龄,SUM(StuAge)年龄总和 from Stuinfo group by StuAddress
UNION
select '男生总数', AVG(StuAge) 平均年龄,SUM(StuAge)年龄总和 from Stuinfo group by StuAddress